from finance.python.repositories import notification_repositories
from .db import get_db_connection, close_connection
import random
import string

async def get_family_members(user_id):
    """
    获取家庭成员列表
    :param user_id: 用户ID
    :return: 家庭成员列表
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            # 查询同一个家庭的所有成员
            sql = """
                SELECT u.id, u.username, u.family_permissions
                FROM users u
                WHERE u.family_type = (
                    SELECT family_type 
                    FROM users 
                    WHERE id = %s
                )
            """
            await cursor.execute(sql, [user_id])
            members = await cursor.fetchall()
            return members
    except Exception as e:
        print(f"获取家庭成员列表失败: {str(e)}")
        return []
    finally:
        await close_connection(connection)

async def get_family_bills(user_id, bill_type=None, type=None, payment_status=None, payment_method=None, member=None):
    """
    获取家庭账单列表
    :param user_id: 用户ID
    :param bill_type: 账单类型
    :param type: 支付类型
    :param payment_status: 支付状态
    :param payment_method: 支付方式
    :param member: 成员ID
    :return: 账单列表
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            # 查询同一个家庭的所有成员的账单
            sql = """
                SELECT b.*, u.username 
                FROM bills b
                JOIN users u ON b.user_id = u.id
                WHERE u.family_type = (
                    SELECT family_type 
                    FROM users 
                    WHERE id = %s
                )
            """
            params = [user_id]
            
            if bill_type:
                sql += " AND b.bill_type LIKE %s"
                params.extend([f"%{bill_type}%"])

            if type is not None:
                sql += " AND b.type = %s"
                params.append(type)

            if payment_status is not None:
                sql += " AND b.payment_status = %s"
                params.append(payment_status)

            if payment_method:
                sql += " AND b.payment_method = %s"
                params.append(payment_method)

            if member:
                sql += " AND b.user_id = %s"
                params.append(member)

            # 按创建时间排序
            sql += " ORDER BY b.created_date DESC"

            await cursor.execute(sql, params)
            bills = await cursor.fetchall()
            return bills
    except Exception as e:
        print(f"获取家庭账单列表失败: {str(e)}")
        return []
    finally:
        await close_connection(connection)

async def get_family_info(family_id):
    """
    获取家庭信息
    :param family_id: 家庭ID
    :return: 家庭信息
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            sql = """
                SELECT id, family_name, family_target
                FROM families 
                WHERE id = %s
            """
            await cursor.execute(sql, [family_id])
            family = await cursor.fetchone()
            return family
    except Exception as e:
        print(f"获取家庭信息失败: {str(e)}")
        return None
    finally:
        await close_connection(connection)

async def get_family_budgets(user_id):
    """
    获取家庭预算列表，合并相同类型的预算
    :param user_id: 用户ID
    :return: 预算列表
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            # 获取同一个家庭的所有预算，按类型合并
            sql = """
                WITH user_budgets AS (
                    SELECT 
                        b.category,
                        b.amount,
                        COALESCE(SUM(bills.amount), 0) as actual_expense,
                        u.username,
                        CASE 
                            WHEN COALESCE(SUM(bills.amount), 0) > b.amount THEN 1
                            ELSE 0
                        END as is_over_budget,
                        b.user_id
                    FROM budgets b
                    LEFT JOIN bills ON b.category = bills.bill_type 
                        AND bills.type = 0 
                        AND bills.user_id = b.user_id
                        AND MONTH(bills.due_date) = MONTH(CURRENT_DATE())
                        AND YEAR(bills.due_date) = YEAR(CURRENT_DATE())
                    JOIN users u ON b.user_id = u.id
                    WHERE b.user_id IN (
                        SELECT id FROM users 
                        WHERE family_type = (
                            SELECT family_type 
                            FROM users 
                            WHERE id = %s
                        )
                    )
                    GROUP BY b.category, b.amount, u.username, b.user_id
                )
                SELECT 
                    category,
                    SUM(amount) as total_amount,
                    SUM(actual_expense) as total_actual_expense,
                    GROUP_CONCAT(
                        CONCAT(
                            username, 
                            ':', 
                            amount,
                            ':', 
                            actual_expense,
                            ':', 
                            is_over_budget
                        )
                    ) as user_details
                FROM user_budgets
                GROUP BY category
                ORDER BY category
            """
            await cursor.execute(sql, [user_id])
            budgets = await cursor.fetchall()
            
            # 处理用户详情字符串
            for budget in budgets:
                users = []
                for user_detail in budget['user_details'].split(','):
                    username, amount, actual_expense, is_over_budget = user_detail.split(':')
                    users.append({
                        'username': username,
                        'amount': float(amount),
                        'actual_expense': float(actual_expense),
                        'is_over_budget': int(is_over_budget) == 1
                    })
                budget['users'] = users
                del budget['user_details']
            
            return budgets
    except Exception as e:
        print(f"获取家庭预算列表失败: {str(e)}")
        return []
    finally:
        await close_connection(connection)

async def remove_family_member(user_id, target_user_id):
    """
    移出/退出家庭
    :param user_id: 操作者ID
    :param target_user_id: 目标用户ID
    :return: 是否成功
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            # 开始事务
            await connection.begin()
            try:
                # 获取操作者权限和家庭信息
                await cursor.execute(
                    "SELECT family_type, family_permissions, username FROM users WHERE id = %s",
                    [user_id]
                )
                operator = await cursor.fetchone()
                if not operator:
                    await connection.rollback()
                    return False

                # 获取目标用户信息
                await cursor.execute(
                    "SELECT family_permissions, username FROM users WHERE id = %s AND family_type = %s",
                    [target_user_id, operator['family_type']]
                )
                target = await cursor.fetchone()
                if not target:
                    await connection.rollback()
                    return False

                # 判断是退出还是移出
                is_self_quit = user_id == target_user_id

                if is_self_quit:
                    # 退出家庭的逻辑
                    if target['family_permissions'] == 0:  # 创建者不能退出
                        await connection.rollback()
                        return False
                else:
                    # 移出家庭的逻辑
                    # 1. 创建者(0)可以移除任何人
                    # 2. 管理员(1)只能移除普通成员(2)
                    # 3. 不能移除创建者
                    if target['family_permissions'] == 0 or \
                       (operator['family_permissions'] == 1 and target['family_permissions'] != 2):
                        await connection.rollback()
                        return False

                # 更新用户家庭信息
                await cursor.execute(
                    """
                    UPDATE users 
                    SET family_type = 0, family_permissions = 0
                    WHERE id = %s
                    """,
                    [target_user_id]
                )

                # 获取需要通知的用户列表
                notify_users = []
                if is_self_quit:
                    # 如果是退出家庭，根据权限发送通知
                    if target['family_permissions'] == 2:  # 普通成员退出
                        # 通知创建者和管理员
                        await cursor.execute(
                            """
                            SELECT id FROM users 
                            WHERE family_type = %s AND family_permissions <= 1
                            """,
                            [operator['family_type']]
                        )
                    else:  # 管理员退出
                        # 只通知创建者
                        await cursor.execute(
                            """
                            SELECT id FROM users 
                            WHERE family_type = %s AND family_permissions = 0
                            """,
                            [operator['family_type']]
                        )
                    notify_users = await cursor.fetchall()

                await connection.commit()

                # 发送系统通知
                if is_self_quit:
                    # 发送退出通知
                    message = f'系统通知：{target["username"]}退出了家庭'
                    for user in notify_users:
                        await notification_repositories.send_system_notification(
                            user['id'],
                            message,
                            is_warning=1  # 红色警告消息
                        )
                else:
                    # 发送移出通知
                    message = f'系统通知：{operator["username"]}将你移出家庭'
                    await notification_repositories.send_system_notification(
                        target_user_id,
                        message,
                        is_warning=1  # 红色警告消息
                    )

                return True

            except Exception as e:
                await connection.rollback()
                print(f"移出/退出家庭事务执行失败: {str(e)}")
                return False

    except Exception as e:
        print(f"移出/退出家庭失败: {str(e)}")
        return False
    finally:
        if connection:
            await close_connection(connection)

async def set_family_admin(user_id, target_user_id, is_promote):
    """
    设置/取消家庭管理员
    :param user_id: 操作者ID
    :param target_user_id: 目标用户ID
    :param is_promote: True为升级为管理员，False为降级为普通成员
    :return: 是否成功
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            # 获取操作者权限和家庭信息
            await cursor.execute(
                "SELECT family_type, family_permissions FROM users WHERE id = %s",
                [user_id]
            )
            operator = await cursor.fetchone()
            if not operator or operator['family_permissions'] != 0:  # 只有创建者可以设置管理员
                return False

            # 获取目标用户信息
            await cursor.execute(
                "SELECT username, family_permissions FROM users WHERE id = %s AND family_type = %s",
                [target_user_id, operator['family_type']]
            )
            target = await cursor.fetchone()
            if not target:
                return False

            # 更新用户权限
            new_permission = 1 if is_promote else 2  # 1为管理员，2为普通成员
            await cursor.execute(
                """
                UPDATE users 
                SET family_permissions = %s
                WHERE id = %s
                """,
                [new_permission, target_user_id]
            )
            await connection.commit()

            # 发送系统通知
            from finance.python.services import notification_service
            await notification_service.send_admin_status_notification(
                target_user_id, 
                target['username'],
                is_promote
            )
            
            return True
    except Exception as e:
        print(f"设置家庭管理员失败: {str(e)}")
        return False
    finally:
        await close_connection(connection)

async def get_user_by_phone(phone):
    """
    通过手机号获取用户信息
    :param phone: 手机号
    :return: 用户信息
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            sql = """
                SELECT id, username, family_type, family_permissions
                FROM users 
                WHERE phone = %s
            """
            await cursor.execute(sql, [phone])
            user = await cursor.fetchone()
            return user
    except Exception as e:
        print(f"获取用户信息失败: {str(e)}")
        return None
    finally:
        await close_connection(connection)

async def get_operator_info(user_id):
    """
    获取操作者信息
    :param user_id: 用户ID
    :return: 操作者信息
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            sql = """
                SELECT id, username, family_type, family_permissions
                FROM users 
                WHERE id = %s
            """
            await cursor.execute(sql, [user_id])
            user = await cursor.fetchone()
            return user
    except Exception as e:
        print(f"获取操作者信息失败: {str(e)}")
        return None
    finally:
        await close_connection(connection)

async def send_family_invite(operator_id, target_phone):
    """
    发送家庭邀请
    :param operator_id: 操作者ID
    :param target_phone: 目标手机号
    :return: 是否成功
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            # 获取目标用户ID
            await cursor.execute(
                "SELECT id FROM users WHERE phone = %s",
                [target_phone]
            )
            target = await cursor.fetchone()
            if not target:
                return False

            # 获取操作者用户名
            await cursor.execute(
                "SELECT username FROM users WHERE id = %s",
                [operator_id]
            )
            operator = await cursor.fetchone()
            if not operator:
                return False

            # 发送系统通知
            message = f"系统通知：{operator['username']}邀请你加入家庭"
            result = await notification_repositories.send_system_notification(
                target['id'],
                message,
                is_warning=3,  # 特殊信息
                sender_id=operator_id  # 添加发送者ID
            )
            return result
    except Exception as e:
        print(f"发送家庭邀请失败: {str(e)}")
        return False
    finally:
        await close_connection(connection)

def generate_invite_code():
    """
    生成8位随机邀请码
    """
    return ''.join(random.choices(string.ascii_uppercase + string.digits, k=8))

async def is_invite_code_exists(cursor, invite_code):
    """
    检查邀请码是否已存在
    """
    await cursor.execute(
        "SELECT id FROM families WHERE invite_code = %s",
        [invite_code]
    )
    return await cursor.fetchone() is not None

async def create_family(user_id: int, family_name: str) -> bool:
    """
    创建家庭
    :param user_id: 用户ID
    :param family_name: 家庭名称
    :return: 是否成功
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            # 开始事务
            await connection.begin()
            try:
                # 检查用户是否已经在家庭中
                await cursor.execute(
                    "SELECT family_type FROM users WHERE id = %s",
                    [user_id]
                )
                user = await cursor.fetchone()
                if not user or user['family_type'] != 0:
                    await connection.rollback()
                    return False

                # 生成邀请码
                invite_code = generate_invite_code()

                # 创建家庭
                await cursor.execute(
                    "INSERT INTO families (family_name, user_id, invite_code) VALUES (%s, %s, %s)",
                    [family_name, user_id, invite_code]
                )
                family_id = cursor.lastrowid

                # 更新用户的家庭信息
                await cursor.execute(
                    """
                    UPDATE users 
                    SET family_type = %s, family_permissions = 0
                    WHERE id = %s
                    """,
                    [family_id, user_id]
                )

                # 提交事务
                await connection.commit()

                # 发送创建成功的通知（在事务提交后）
                message = f"系统通知：创建家庭'{family_name}'成功"
                await notification_repositories.send_system_notification(
                    user_id,
                    message,
                    is_warning=2  # 绿色成功消息
                )

                return True

            except Exception as e:
                await connection.rollback()
                print(f"创建家庭事务执行失败: {str(e)}")
                return False

    except Exception as e:
        print(f"创建家庭失败: {str(e)}")
        return False
    finally:
        if connection:
            await close_connection(connection)

async def check_family(user_id: int):
    """
    查询用户家庭状态
    :param user_id: 用户ID
    :return: 用户家庭信息
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            # 查询用户的家庭信息
            sql = """
                SELECT u.family_type, u.family_permissions, f.family_name, f.invite_code
                FROM users u
                LEFT JOIN families f ON u.family_type = f.id
                WHERE u.id = %s
            """
            await cursor.execute(sql, [user_id])
            result = await cursor.fetchone()
            return result
    except Exception as e:
        print(f"查询用户家庭状态失败: {str(e)}")
        return None
    finally:
        await close_connection(connection)

async def delete_family(user_id: int) -> bool:
    """
    删除家庭
    :param user_id: 用户ID
    :return: 是否成功
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            # 开始事务
            await connection.begin()
            try:
                # 1. 检查用户是否是创建者，并获取家庭信息
                await cursor.execute(
                    """
                    SELECT u.family_type, u.family_permissions, u.username, f.family_name
                    FROM users u
                    LEFT JOIN families f ON u.family_type = f.id
                    WHERE u.id = %s
                    """,
                    [user_id]
                )
                user = await cursor.fetchone()
                if not user or user['family_permissions'] != 0 or user['family_type'] == 0:
                    await connection.rollback()
                    return False

                family_id = user['family_type']
                family_name = user['family_name']
                creator_name = user['username']

                # 2. 获取所有家庭成员
                await cursor.execute(
                    "SELECT id FROM users WHERE family_type = %s",
                    [family_id]
                )
                members = await cursor.fetchall()

                # 3. 删除家庭
                await cursor.execute(
                    "DELETE FROM families WHERE id = %s",
                    [family_id]
                )

                # 4. 更新所有成员的家庭信息
                await cursor.execute(
                    """
                    UPDATE users 
                    SET family_type = 0, family_permissions = 0
                    WHERE family_type = %s
                    """,
                    [family_id]
                )

                # 提交事务
                await connection.commit()

                # 5. 发送系统通知（在事务提交后）
                message = f'系统通知：创建者"{creator_name}"已将家庭"{family_name}"解散'
                for member in members:
                    await notification_repositories.send_system_notification(
                        member['id'],
                        message,
                        is_warning=1  # 红色警告消息
                    )

                return True

            except Exception as e:
                await connection.rollback()
                print(f"删除家庭事务执行失败: {str(e)}")
                return False

    except Exception as e:
        print(f"删除家庭失败: {str(e)}")
        return False
    finally:
        if connection:
            await close_connection(connection)


# 加入家庭
async def join_family(user_id, invite_code):
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            # 查询邀请码家庭
            sql = """
                SELECT user_id
                FROM families
                WHERE invite_code = %s
            """
            await cursor.execute(sql, [invite_code])
            result = await cursor.fetchone()
            if result:
                target_id = result["user_id"]
                async with connection.cursor() as cursor:
                    # 获取操作者用户名
                    await cursor.execute(
                        "SELECT username FROM users WHERE id = %s",
                        [user_id]
                    )
                    operator = await cursor.fetchone()
                    if not operator:
                        return False

                    # 发送系统通知
                    message = f"系统通知：{operator['username']}申请加入家庭"
                    result = await notification_repositories.send_system_notification(
                        target_id,
                        message,
                        is_warning=3,
                        sender_id=user_id
                    )

                    return result
            return False
    except Exception as e:
        print(f"查询用户家庭状态失败: {str(e)}")
        return None
    finally:
        await close_connection(connection)
